Prior art query optimizers typically rely upon a cost model to choose the best query execution plan for any given query. While prior art query optimizers do a remarkably good job of estimating both the cost and the cardinality of most queries, many assumptions underlie this mathematical model. For example, cost estimates are heavily dependent upon the optimizer's estimates for the number of rows (cardinalities), which will result at each step of the query plan. For complex queries involving many predicates and/or operations, these estimates rely upon statistics on the database and modeling assumptions that may or may not be true for a given database. In other words, estimates rely on accurate statistics of database characteristics, beginning with the number of rows for each table or index.
In most optimizers, alternative query execution plans are generated for a given query and the optimizer selects the optimal query plan, which may be the most cost-effective execution plan or one of the more cost-effective execution plans. The optimizer identifies an execution plan (query plan, join plan, or strategy) that reduces the estimated response time of a given query. The response time is the amount of time it takes to complete the execution of the query on the given target system.
One technique of query optimization use is a cost model to estimate the response time of a given query plan and to search the space of query plans to return a plan with a low cost. In the cost-based optimization model, different methods for doing a unit of work is compared and the most efficient method is selected (the plan with the lowest cost). Because the number of alternatives can be quite large, the query optimizer module uses statistics and/or sampling techniques to reduce the search space in optimizing queries.
However, prior art optimizers fail to consider the impact of run-time statistics (real-time system conditions), as they do not manage unforeseen impacts, such as unplanned situations (e.g. a request volume surge, the exhaustion of shared resources, or external conditions like component outages) or even planned situations (e.g. systems maintenance or data load). For example, modern computing systems often execute a variety of requests concurrently and operate in a dynamic environment of cooperative systems, each comprising of numerous hardware components subject to failure or degradation.
Many different types of system conditions or operating environment events can impact negatively the performance of requests currently executing on a computer system. These conditions or events can remain undetected for a prolonged period of time, causing a compounding negative effect on requests executing during that interval. When problematic conditions or events are detected, sometimes in an ad hoc and manual fashion, the computing system administrator may still not be able to take an appropriate course of action, and may either delay corrective action, act incorrectly or not act at all.
A typical impact of not managing for conditions or events is to deliver inconsistent response times to users. For example, often systems execute in an environment of very cyclical usage over the course of any day, week, or other business cycle. If a user ran a report near standalone on a Wednesday afternoon, he/she may expect that same performance with many concurrent users on a Monday morning. However, based on the laws of linear systems performance, a request simply cannot deliver the same response time when running stand-alone as when it runs competing with high volumes of concurrency.
Therefore, prior art query optimization can be effective in a controlled environment (without external impacts); however, it fails to respond effectively when those external impacts are present. In other words, the problem of dynamically adjusting query plans to real-time system conditions and operating environment events in order to achieve a set of service level goals for complex relational database systems is an extremely challenging problem.
What is needed are improved solutions to this problem. The present invention satisfies this need.